﻿'Description:周期性检测基础数据类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhangrj
'create Date:2023-01-09
'Rewriter:
'Rewrite Date:
Imports BusinessRule.DTO
Imports External.APICalling
Imports DatabaseAccess.DBUtility
Imports System.Text

Public Class PeriodicCheckBusiness
    ''' <summary>
    ''' 获取周期性检测基础数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetPeriodicCheckListByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql = <string>
                         SELECT pc.productname,pc.specno,pc.opdesc,pc.detectiontype,DECODE(pc.isenable,1,'是','否') isenablename,pc.effectivetype, pc.effectivecount,
                         (CASE WHEN pc.duedate IS NULL THEN pc.surplusqty ELSE ceil(pc.duedate - SYSDATE) END) effective,
                         pc.duedate,pc.surplusqty,pc.notes,pc.periodiccheckbasedataid,pf.productfamilyname,pca.periodiccheckapplyid
                         FROM PeriodicCheckBaseData pc 
                         LEFT JOIN PeriodicCheckApply pca ON pca.periodiccheckbasedataid = pc.periodiccheckbasedataid AND pca.isuse = 0 AND pca.status = 1
                         LEFT JOIN productbase pb ON pb.productname = pc.productname
                         LEFT JOIN product p ON p.productid = pb.revofrcdid
                         LEFT JOIN productfamily pf ON pf.productfamilyid = p.productfamilyid
                         WHERE pc.isuse = 0 and pc.factoryid = '{0}'
         </string>.Value

        strSql = String.Format(strSql, para("FactoryID"))

        If para.ContainsKey("ProductName") Then
            If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
                strSql += String.Format(" and pc.productname like '%{0}%'", para("ProductName"))
            End If
        End If

        If para.ContainsKey("SpecNo") Then
            If Not String.IsNullOrWhiteSpace(para("SpecNo")) Then
                strSql += String.Format(" and pc.specno = '{0}' ", para("SpecNo"))
            End If
        End If

        If para.ContainsKey("SpecDesc") Then
            If Not String.IsNullOrWhiteSpace(para("SpecDesc")) Then
                strSql += String.Format(" and pc.opdesc = '{0}' ", para("SpecDesc"))
            End If
        End If

        If para.ContainsKey("WorkCenterID") Then
            If Not String.IsNullOrWhiteSpace(para("WorkCenterID")) Then
                strSql += String.Format(" and pc.WorkCenterID = '{0}' ", para("WorkCenterID"))
            End If
        End If

        If para.ContainsKey("TeamID") Then
            If Not String.IsNullOrWhiteSpace(para("TeamID")) Then
                strSql += String.Format(" and pc.TeamID = '{0}' ", para("TeamID"))
            End If
        End If

        If para("Status") = "1" Then '有效
            strSql = "SELECT t.*,'有效' status,(CASE WHEN t.effective < 0 and t.duedate IS NOT NULL THEN 0 WHEN t.duedate IS NULL THEN t.effectivecount ELSE t.effective END) || t.effectivetype effectivename FROM( " + strSql + ")t WHERE t.periodiccheckapplyid IS NULL AND t.effective > 0"
        ElseIf para("Status") = "2" Then '检测中
            strSql = "SELECT t.*,'检测中' status,(CASE WHEN t.effective < 0 and t.duedate IS NOT NULL THEN 0 WHEN t.duedate IS NULL THEN t.effectivecount ELSE t.effective END) || t.effectivetype effectivename FROM( " + strSql + ")t WHERE t.periodiccheckapplyid IS NOT NULL"
        ElseIf para("Status") = "3" Then '到期
            strSql = "SELECT t.*,'到期' status,(CASE WHEN t.effective < 0 and t.duedate IS NOT NULL THEN 0 WHEN t.duedate IS NULL THEN t.effectivecount ELSE t.effective END) || t.effectivetype effectivename FROM( " + strSql + ")t WHERE t.periodiccheckapplyid IS NULL AND t.effective <= 0"
        Else '全部查询
            strSql = "SELECT t.*,(CASE WHEN t.periodiccheckapplyid IS NULL AND t.effective>0 THEN '有效' WHEN t.periodiccheckapplyid IS NULL AND t.effective <= 0  THEN '到期' ELSE '检测中' END) status,(CASE WHEN t.effective < 0 and t.duedate IS NOT NULL THEN 0 WHEN t.duedate IS NULL THEN t.effectivecount ELSE t.effective END) || t.effectivetype effectivename FROM (" + strSql + ")t"
        End If

        strSql += " ORDER BY t.productname,t.specno"

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageNumber").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取委托单信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetPeriodicCheckApplyDataByPage(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim strSql = <string>

SELECT t.*,CASE WHEN t.status = 1 THEN '检测中' WHEN t.status = 2 OR t.effective {0} 0 THEN '到期' ELSE '有效' END statusname 
FROM (
SELECT p.*,(CASE WHEN pb.duedate IS NULL THEN pb.surplusqty ELSE ceil(pb.duedate - SYSDATE) END) effective
            ,e.fullname CREATEEMPNAME
             FROM PeriodicCheckApply p 
             LEFT JOIN employee e ON e.employeeid = p.createempid
             LEFT JOIN PeriodicCheckBaseData pb ON pb.periodiccheckbasedataid = p.periodiccheckbasedataid
            WHERE p.periodiccheckbasedataid = '{1}' AND p.isuse = 0 
            )t
            ORDER BY t.Createdate DESC
         </string>.Value

        strSql = String.Format(strSql, "<=", para("PeriodicCheckBaseDataId"))

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("PageNumber"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize"))
        paraPage.strSQL = strSql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 验证是否有效的周期性数据（新增、导入数据校验）
    ''' </summary>
    ''' <param name="productName"></param>
    ''' <param name="specNo"></param>
    ''' <param name="specDesc"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsVaildPeriodicCheck(ByVal productName As String, ByVal specNo As String, ByVal specDesc As String, ByRef msg As String) As Boolean
        '检查是否存在周期性检测基础数据
        Dim sql = "SELECT 1 FROM periodicCheckBaseData pc WHERE pc.productname = '" + productName + "' AND pc.specno = '" + specNo + "' and pc.isuse = 0"
        If Not DatabaseHelper.GetSingle(sql) Is Nothing Then
            msg = "已存在【件号：" + productName + "，工序号：" + specNo + "】的检测数据"
            Return False
            Exit Function
        End If
        '检测是否存在件号、工序基础数据
        sql = <string>
                  SELECT 1 FROM productbase pb
                  LEFT JOIN product p ON p.productid = pb.revofrcdid
                  LEFT JOIN Workflow w ON w.workflowid = p.workflowid
                  LEFT JOIN Workflowstep ws ON ws.workflowid = w.workflowid
                  LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                  LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                  WHERE pb.productname = '{0}' 
                  AND s.specno = '{1}' AND s.opdesc = '{2}'
              </string>.Value

        If DatabaseHelper.GetSingle(String.Format(sql, productName, specNo, specDesc)) Is Nothing Then
            msg = "系统不存在【件号：" + productName + "，工序号：" + specNo + "，工序名：" + specDesc + "】的基础数据"
            Return False
            Exit Function
        End If
        Return True
    End Function
    ''' <summary>
    ''' 检测是否有效的工区班组（导入验证）
    ''' </summary>
    ''' <param name="workCenterName"></param>
    ''' <param name="teamName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsVaildWorkCenter(ByVal factoryID As String, ByVal workCenterName As String, ByVal teamName As String) As Tuple(Of Boolean, String, String)
        Dim strSql = <string>
 SELECT w.workcenterid,t.teamid FROM Workcenter w 
LEFT JOIN team t ON t.workcenterid = w.workcenterid
WHERE w.workcentername = '{0}' AND t.teamname = '{1}' AND w.factoryid = '{2}'
                     </string>.Value
        strSql = String.Format(strSql, workCenterName, teamName, factoryID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count = 0 Then
            Return New Tuple(Of Boolean, String, String)(False, "", "")
            Exit Function
        End If
        Return New Tuple(Of Boolean, String, String)(True, dt.Rows(0)("workcenterid").ToString(), dt.Rows(0)("teamid").ToString())
    End Function

    ''' <summary>
    ''' 更新周期性检测数据状态（删除，启停）
    ''' </summary>
    ''' <param name="dtPC"></param>
    ''' <param name="type"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdatePeriodicCheckStatus(ByVal dtPC As DataTable, ByVal type As Integer, ByVal userInfo As CurrentUserInfo) As uMESProcessResult

        Dim result As New uMESProcessResult
        result.Result = True
        result.Message = "操作成功"

        Dim strSql = "SELECT 1 FROM PeriodicCheckApply pca WHERE pca.periodiccheckbasedataid in ({0}) AND pca.isuse = 0 AND pca.status = 1"
        Dim ids = ""
        For Each dr As DataRow In dtPC.Rows
            ids += "'" + dr("periodiccheckbasedataid").ToString + "',"
        Next
        ids = ids.Trim(",")
        '验证更新的数据中是否存在送检中的状态
        If Not DatabaseHelper.GetSingle(String.Format(strSql, ids)) Is Nothing Then
            result.Result = False
            result.Message = "存在检测中的数据"
            Return result
            Exit Function
        End If
        '开始更新操作
        Dim sqlList As New ArrayList
        If type = 1 Then '删除
            strSql = String.Format("update PeriodicCheckBaseData b set b.isuse = 1,b.lastchangedate=SYSDATE where b.periodiccheckbasedataid in ({0})", ids)
            sqlList.Add(strSql)
            strSql = String.Format("update PeriodicCheckApply pca set pca.isuse = 1,pca.lastchangedate=SYSDATE,pca.lastchangeempid='{0}' where pca.periodiccheckbasedataid in ({1})", userInfo.EmployeeID.ToString(), ids)
            sqlList.Add(strSql)
        ElseIf type = 2 Then '启停
            strSql = String.Format("update PeriodicCheckBaseData b set b.isenable=DECODE(b.isenable,1,0,1),b.lastchangedate=SYSDATE where b.periodiccheckbasedataid in ({0})", ids)
            sqlList.Add(strSql)
        Else
            result.Result = False
            result.Message = "无效操作"
        End If

        If result.Result Then
            DatabaseHelper.ExecuteSqlTran(sqlList)
        End If
        Return result
    End Function

    ''' <summary>
    ''' 验证报工时，批次的件号和工序号是否是有效的周期性数据，并做数据更改
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="msg"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsVaildPeriodicForReport(ByVal para As Dictionary(Of String, String), ByRef msg As String) As Boolean
        Dim res = True
        Dim strSql = <string>
SELECT (CASE WHEN pc.duedate IS NULL THEN pc.surplusqty ELSE ceil(pc.duedate - SYSDATE) END) effective,
pc.effectivetype,pc.surplusqty,pc.productname,pc.specno,pc.periodiccheckbasedataid,pa.periodiccheckapplyid
FROM Workflowstep ws
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid) 
LEFT JOIN PeriodicCheckBaseData pc ON pc.specno = s.specno AND pc.isenable = 1 AND pc.isuse = 0
LEFT JOIN periodicCheckApply pa ON pa.periodiccheckbasedataid = pc.periodiccheckbasedataid AND pa.isuse = 0 AND pa.status = 1
WHERE pc.productname = '{0}' AND ws.workflowstepid = '{1}'
                     </string>.Value
        strSql = String.Format(strSql, para("ProductName"), para("WorkflowStepID"))
        Dim dtCheck As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        '如果不存在周期性检测，则跳过不验证
        If dtCheck.Rows.Count = 0 Then
            Return True
            Exit Function
        End If
        '验证数据
        Dim drCheck As DataRow = dtCheck.Rows(0)
        If Not String.IsNullOrWhiteSpace(drCheck("periodiccheckapplyid").ToString()) Then '如果存在送检中，则提示
            msg = "件号【" + drCheck("productname").ToString() + "】工序号【" + drCheck("specno").ToString() + "】存在送检中的周期性检测数据"
            res = False
        ElseIf Integer.Parse(drCheck("effective").ToString()) <= 0 Then '如果过期
            msg = "件号【" + drCheck("productname").ToString() + "】工序号【" + drCheck("specno").ToString() + "】存在过期的周期性检测数据"
            res = False
            '更新委托表为过期状态
            strSql = String.Format("UPDATE periodicCheckApply pa SET pa.status = 2 WHERE pa.periodiccheckbasedataid = '{0}'", drCheck("periodiccheckbasedataid").ToString())
            DatabaseHelper.ExecuteSql(strSql)
        ElseIf drCheck("effectivetype").ToString() = "件" Then '如果是件，则判定报工数量
            '如果报工数大于剩余数量
            If Integer.Parse(drCheck("surplusqty").ToString()) < Integer.Parse(para("ReportQty")) Then
                msg = "件号【" + drCheck("productname").ToString() + "】工序号【" + drCheck("specno").ToString() + "】周期性检测剩余数量为：" + drCheck("surplusqty").ToString() + "件，小于报工数量"
                res = False
            End If
        End If
        Return res
    End Function

    ''' <summary>
    ''' 更新周期性检测剩余数量
    ''' </summary>
    ''' <param name="productName"></param>
    ''' <param name="workflowstepID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdatePeriodicCheckSurplusQty(ByVal productName As String, ByVal workflowstepID As String, ByVal reportQty As Integer) As String
        Dim strSql = <string>
UPDATE PeriodicCheckBaseData pc SET pc.surplusqty = pc.surplusqty - {0}
WHERE pc.periodiccheckbasedataid = 
(SELECT pc1.periodiccheckbasedataid FROM Workflowstep ws
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid) 
LEFT JOIN PeriodicCheckBaseData pc1 ON pc1.specno = s.specno AND pc1.isenable = 1 AND pc1.isuse = 0
WHERE pc1.productname = '{1}' AND ws.workflowstepid = '{2}' and pc1.effectivetype = '件')
                     </string>.Value
        strSql = String.Format(strSql, reportQty, productName, workflowstepID)
        Return strSql
    End Function
End Class
